<?php
namespace Admins\Model;
use Think\Model;
class PHPExcelModel extends Model {
	protected $tableName = 'promoter_dataoke_product';
	
	public function _initialize()
	{
		set_time_limit(900);
		ini_set("memory_limit", "1024M");
		vendor('PHPExcel.PHPExcel','','.php');
	}
	public function getExcelAllData($filePath)
	{
		vendor('PHPExcel.PHPExcel.IOFactory','','.php');
		//设置制定的列字段
		$inputFileType = 'Excel5';
		$objPHPExcel = \PHPExcel_IOFactory::load($filePath); 
		$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
		//var_dump($sheetData);
		return $sheetData;	
		
		
	}
	public function getExcelData($filePath)
	{
		vendor('PHPExcel.PHPExcel.IOFactory','','.php');
		$inputFileType = 'Excel5';
		$objPHPExcel = \PHPExcel_IOFactory::load($filePath); 

			// 读取规则 
		$sheet_read_arr = array(); 
		$sheet_read_arr["Page1"] = array("A","K","L","S","M","T"); 
		//$sheet_read_arr["sheet2"] = array("A","B","C","D","F"); 
 
		// 循环所有的页 
		foreach ($sheet_read_arr as $key => $val) 
		{ 
			$currentSheet = $objPHPExcel->getSheetByName($key);// 通过页名称取得当前页 
			$row_num = $currentSheet->getHighestRow();// 当前页行数 
		 	$result = array();
			
			// 循环从第二行开始，第一行往往是表头 
			for ($i = 1; $i <= $row_num; $i++) 
			{ 
				$cell_values = array(); 
				foreach ($val as $cell_val) 
				{ 
					$address = $cell_val . $i;// 单元格坐标 
		 
					// 读取单元格内容 
					$cell_values[] = $currentSheet->getCell($address)->getFormattedValue(); 
				} 
		 
				// 看看数据 
				$result[] = $cell_values;
			} 
			return $result;
		} 
		
	
	
	
		
	}
	
	public function onlyCheck($GoodsID,$groupId)
	{//检查数据库字段的唯一性
		$sql = "select id from ".C('DB_PREFIX')."promoter_dataoke_product where goodsId = '".$GoodsID."' and groupId='".$groupId."'";
		$data = $this->query($sql);	
		//$data = $this->execute($sql);	
		if($data)
		{
			return $data[0];
		}
		else
		{
			return false;
		}
		
	
	}	
	
	
	
	public function moreExecute($arrSql)
	{//数组形式接受sql语句
		$sql = $arrSql[0];
		$data = $this->execute($sql);	
		if($data)
		{
			return true;
		}
		else
		{
			return false;
		}		
		
	
	}
	public function getInsertSql($data)
	{//只返回sql语句,不执行
		
		$sql = $this->fetchSql(true)->add($data);
		return $sql;	
	}
	public function getUpdateSql($data)
	{
		$sql = $this->fetchSql(true)->save($data);
		return $sql;		
	}
	public function oneInsert()
	{
	}
	public function oneUpdate()
	{
	}
	public function moreInsert($data)
	{//批量插入数据
		$result = $this->addAll($data);
		return $result;	
	}




	public function insertData($xlsFile,$groupId)
	{
	
		$inputFileName = './Uploads/'.$xlsFile;

		$res = $this->getExcelAllData($inputFileName);
		if(count($res)<2) return false;
		$conf = $res[1];
		//var_dump($conf );
		
		//var_dump($res);
		//获取读取的规则--配置有关系
		
		$fields = c("XLSFIELD");
		foreach ($fields as $k=>$v)
		{
			$ruleField[$k] = $v;
		}
//		$ruleField['goodsID'] = c("XLSFIELD.goodsId");
//		$ruleField['taokeShortUrl'] = c("XLSFIELD.taokeShortUrl");
//		$ruleField['quanUrl'] = c("XLSFIELD.quanUrl");
//		$ruleField['taokeUrl'] = c("XLSFIELD.taokeUrl");
//		$ruleField['taoKouling'] = c("XLSFIELD.taoKouling");
//		$ruleField['quanKouling'] = c("XLSFIELD.quanKouling");
		
		
		$rowKey = getArrKey($conf,$ruleField);
		//var_dump($rowKey);
			
		unset($res[1]); //删除表头
		
		//定义新的录入数据格式
		$data = '';
		foreach($res as $k=>$v)
		{
			$product['groupId'] =  get_basename($xlsFile);
			$product['goodsId'] = $v[$rowKey['goodsId']];
			$product['taokeShortUrl'] = $v[$rowKey['taokeShortUrl']];
			$product['quanShortUrl'] = "";//因为特殊性，判断可以了再赋值			
			//$product['quanUrl'] = $v[$rowKey['quanUrl']];//长连接，这个也先不保存数据库 没用而且占空间
			//$product['taokeUrl'] = $v[$rowKey['taokeUrl']];//长连接，这个也先不保存数据库 没用而且占空间
			$product['taoKouling'] = $v[$rowKey['taoKouling']];
			$product['quanKouling'] = "";//因为特殊性，判断可以了再赋值
			
			$product['fansbuyUrl'] ='';//存 二合一的连接
			$product['fansbuyShortUrl']='';//短链接
			$product['fansKongling']='';//拼接链接淘口令
			$product['Price'] = $v[$rowKey['Price']];//读取xls的这个字段，不保存数据库，只是为了判断 券能不能用
			$product['quanPrice'] = $v[$rowKey['quanPrice']];//因为特殊性，后面正则出来了 重新赋值 判断可以了再赋值
			
			$product['time'] = date('Y-m-d H:i:s');
			
			//在这里是整理好的xls数据,但最后一步是 确定券可以用，也就是说这里判断一下，券可以用了再写入数据库
			// 换句话说，保存到数据库的二合一券都是可以用的
			$limitPrice = 0;
			preg_match_all("/\d+/", $product['quanPrice'], $matches);
			$matches = $matches[0];
			//var_dump($matches);
			if($matches<>NULL && count($matches)>=2)
			{
				$limitPrice = $matches[0];
				$quanPrice = $matches[1];
			}
			else
			{
				$product['quanPrice'] = "";//这里可以把它赋值空了
			}

			if($limitPrice<>NULL && $product['Price']>=$limitPrice && count($matches)>=2)
			{//表示券能用，quanShortUrl,券口令都为空 券价格 可以赋值了		
				//echo $product['Price']."能用我要赋值了<br>";
				$product['quanShortUrl'] = $v[$rowKey['quanShortUrl']];
				$product['quanKouling'] = $v[$rowKey['quanKouling']];
				$product['quanPrice'] = $quanPrice;
			}
									
			//---下面内容是拼接二合一链接
			//获取item 和券id 和PID 和 高佣 定向
			$DataokeApi = new \Admins\Model\DataokeApiModel();
			$info = $DataokeApi->getOneData($product['goodsId']);
			if($info)
			{//如果可以查到信息，就进行拼装二合一链接
				$info = $info[0];
				//var_dump($info);
				$GoodsID = $info['goodsid'];
				$activityId = $info['quan_id'];
				$Promoter = new \Admins\Model\PromoterModel();
				$promoterInfo = $Promoter->getOneData($product['groupId']);
				$pid = "mm_33549446_18174221_64892431";
				if($promoterInfo )
				{
					$promoterInfo = $promoterInfo[0];
					$pid = $promoterInfo['pid'];
				}
				$dx = 1;
				if($info['commission_queqiao'] >= $info['commission_jihua'])
				{
					$dx = "";
				}
				$product['fansbuyUrl'] = get_fansbuyUrl($GoodsID,$activityId,$pid,$dx);
				//$product['fansbuyShortUrl'] = get_shortUrl($product['fansbuyUrl']);
				
				//下面字段只为淘口令做准备
				//$product['Picimg'] = $v[$rowKey['Picimg']];
				//$product['goodsTitle'] = $v[$rowKey['goodsTitle']];
				//$product['fansKongling'] = get_fansKouling($product['goodsTitle'],$product['Picimg'],$product['fansbuyUrl']);
				
			}
			
			$data[$k] = $product;
		}
		//var_dump($data);
	//exit;	
//------------------------------
		$sql = '';	
		foreach($data as $v)
		{

			$isNewdata = $this->onlyCheck($v['goodsId'],$groupId);
			
			if($isNewdata)
			{//更新操作语句
				$v['id'] = $isNewdata['id'];
				$sql .= $this->getUpdateSql($v).';';
			}
			else
			{//插入操作语句
				
				$sql .= $this->getInsertSql($v).';';
				
				
			}		
		}
		//echo $sql;
		//定义一个数组，把这些sql语句以一个引用（指针）传过去，不然直接把字符串当参数太大，太消耗内存了。
		$arraySql[] = $sql;
		//echo $arraySql;
		$result = $this->moreExecute($arraySql);
		return $result;
		
	}
	
	public function getZhoushouUpdateSql($data)
	{
		//$sql = $this->fetchSql(true)->add($data);
		$sql = "UPDATE ".C('DB_PREFIX')."promoter_dataoke_product set `groupId`='".$data['groupId']."',`goodsId`='".$data['goodsId']."',`taokeShortUrl`='".$data['taokeShortUrl']."',`time`='".$data['time']."' where `goodsId`='".$data['goodsId']."' and `groupId`='".$data['groupId']."'";
		return $sql;	
	}
	public function getZhoushouInsertSql($data)
	{
		//$sql = $this->fetchSql(true)->save($data);
		$sql = "insert into ".C('DB_PREFIX')."promoter_dataoke_product (groupId,goodsId,taokeShortUrl,time)value('".$data['groupId']."','".$data['goodsId']."','".$data['taokeShortUrl']."','".$data['time']."')";
		return $sql;	
	}
	
	
	
	
	
	
	
}